Creating Measures 9

Create the following DAX Measures

 

1.     1.   .Total Hire.refactor Interested =

CALCULATE (

    DISTINCTCOUNT ( JobSubmissions[JobID] ),

    FILTER (

       JobSubmissions,

        (JobSubmissions[JS_ID] = 13

            || JobSubmissions[JS_ID] = 14)

           && JobSubmissions[SourceID] = 3))

 

2.   2.    .Total Hire.refactored.ai Submission _new =

CALCULATE (

    DISTINCTCOUNT ( JobSubmissions[JobID] ),

    FILTER (

        JobSubmissions,

       ( JobSubmissions[JS_ID] = 1

       || JobSubmissions[JS_ID] = 6

       || JobSubmissions[JS_ID] = 12

       || JobSubmissions[JS_ID] = 15)

       && JobSubmissions[SourceID] = 3

         

    )

       )

 

3.     3.  .Total Huntr Interested =

CALCULATE (

    DISTINCTCOUNT ( JobSubmissions[JobID] ),

    FILTER (

       JobSubmissions,

        (JobSubmissions[JS_ID] = 13

            || JobSubmissions[JS_ID] = 14)

            && JobSubmissions[SourceID] = 4))

 

 

4.     4.  .Total Huntr Interview =

CALCULATE (

    DISTINCTCOUNT ( JobSubmissions[JobID] ),

        FILTER (

        JobSubmissions,

       (JobSubmissions[JS_ID] = 2

           || JobSubmissions[JS_ID] = 3

           || JobSubmissions[JS_ID] = 4)

           && JobSubmissions[SourceID] = 4

    )

       )

 

5.      5. .Total Huntr Submission =

CALCULATE (

   DISTINCTCOUNT ( JobSubmissions[JobID] ),

    FILTER (

        JobSubmissions,

       ( JobSubmissions[JS_ID] = 1

       || JobSubmissions[JS_ID] = 6

    || JobSubmissions[JS_ID] = 12

       || JobSubmissions[JS_ID] = 15)

        && JobSubmissions[SourceID] = 4

         

    )

)

 

6.    6.   .Total Huntr Submission_new =

CALCULATE(COUNT(JobSubmissions[JobID]),

    FILTER (

        JobSubmissions, JobSubmissions[SourceID] = 4          

    )

     )


7. .Arrow Indicator Interest =

VAR Up =
UNICHAR ( 9650 )
VAR Down =
UNICHAR ( 9660 )
RETURN
IF ([Total Interest] < 0, Down,
IF([Total Interest] > 0, Up, " " )

)

8..Arrow Indicator InterTOPlcm =
VAR Up =
UNICHAR ( 9650 )
VAR Down =
UNICHAR ( 9660 )
RETURN
IF ( [Rolling Month INtvtoPlcmnt] < 0, Down,
IF([Rolling Month INtvtoPlcmnt] > 0, Up, " " )

)

9. .Arrow Indicator InterToPlcmWeek =

VAR Up =
UNICHAR ( 9650 )
VAR Down =
UNICHAR ( 9660 )
VAR Color = IF(Up, "Green", "Red")
RETURN
IF ([Rolling Week IntrvtoPlcmnt] < 0, Down,
IF([Rolling Week IntrvtoPlcmnt] > 0, Up, " " )

)

10. .Arrow Indicator IntrTosubMonth =
VAR Up =
UNICHAR ( 9650 )
VAR Down =
UNICHAR ( 9660 )
VAR Color = IF(Up, "Green", "Red")
RETURN
IF ( [Rolling Month InttoSubm] < 0, Down,
IF([Rolling Month InttoSubm] > 0, Up, " " )

)

11. .Arrow Indicator IntrTosubWeek =
VAR Up =
UNICHAR ( 9650 )
VAR Down =
UNICHAR ( 9660 )
VAR Color = IF(Up, "Green", "Red")
RETURN
IF ( [Rolling Week InttoSubm] < 0, Down,
IF([Rolling Month InttoSubm] > 0, Up, " " )

)

12. .Arrow Indicator SubToIntrMonth =
VAR Up =
UNICHAR ( 9650 )
VAR Down =
UNICHAR ( 9660 )
RETURN
IF ( [Rolling Month SubmtoIntrv] < 0, Down,
IF([Rolling Month SubmtoIntrv] > 0, Up, " " )

)

13. .Arrow Indicator SubToIntWeek =
VAR Up =
UNICHAR ( 9650 )
VAR Down =
UNICHAR ( 9660 )
VAR Color = IF(Up, "Green", "Red")
RETURN
IF ([Rolling Week Sub to Intrview] < 0, Down,
IF([Rolling Week Sub to Intrview] > 0, Up, " " )

)

14. .Gauging Interest =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
JobSubmissions[JS_ID] = 13
|| JobSubmissions[JS_ID] = 14
|| JobSubmissions[JS_ID] = 1
|| JobSubmissions[JS_ID] = 16
)
)

15. cc.Intiating Contact =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
JobSubmissions[JS_ID] = 3
|| JobSubmissions[JS_ID] = 2
|| JobSubmissions[JS_ID] = 4
)
)

16. .Last Month Interview to Placement Ratio =
CALCULATE ( DIVIDE ( [Total PLacement], [Total Interview] ), ( DATESINPERIOD ( 'Date'[Date], TODAY (), -1, MONTH ) ) )

17. .LM Submission to Interview Ratio = CALCULATE ( DIVIDE ( [Total Interview], [Total Submission] ), ( DATESINPERIOD ( 'Date'[Date], TODAY (), -1, MONTH ) ) )

18. .Not Promising =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
JobSubmissions[JS_ID] = 9
|| JobSubmissions[JS_ID] = 15
|| JobSubmissions[JS_ID] = 10
|| JobSubmissions[JS_ID] = 6
|| JobSubmissions[JS_ID] = 11
)
)

19. .Promising =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
JobSubmissions[JS_ID] = 5
|| JobSubmissions[JS_ID] = 8
|| JobSubmissions[JS_ID] = 7
)
)

20. .Total Hire.refactored.ai Interview =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
(JobSubmissions[JS_ID] = 2
|| JobSubmissions[JS_ID] = 3
|| JobSubmissions[JS_ID] = 4)
&& JobSubmissions[SourceID] = 3
)
)

21. .Total Hire.refactored.ai Submission _new =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
( JobSubmissions[JS_ID] = 1
|| JobSubmissions[JS_ID] = 6
|| JobSubmissions[JS_ID] = 12
|| JobSubmissions[JS_ID] = 15)
&& JobSubmissions[SourceID] = 3
)
)

22. .Total Huntr Interview =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
(JobSubmissions[JS_ID] = 2
|| JobSubmissions[JS_ID] = 3
|| JobSubmissions[JS_ID] = 4)
&& JobSubmissions[SourceID] = 4
)
)

.Total Huntr Submission =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
( JobSubmissions[JS_ID] = 1
|| JobSubmissions[JS_ID] = 6
|| JobSubmissions[JS_ID] = 12
|| JobSubmissions[JS_ID] = 15)
&& JobSubmissions[SourceID] = 4
)
)

23. .Total Huntr Submission_new =
CALCULATE(COUNT(JobSubmissions[JobID]),
FILTER (
JobSubmissions, JobSubmissions[SourceID] = 4
)
)

24. .Total Sales Rep Interested =
CALCULATE ( DISTINCTCOUNT ( JobSubmissions[JobID] ), FILTER ( JobSubmissions, ( JobSubmissions[JS_ID] = 13 || JobSubmissions[JS_ID] = 14 ) && JobSubmissions[SourceID] = 2 ) )


25. .Total Sales Rep Interview =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
(JobSubmissions[JS_ID] = 2
|| JobSubmissions[JS_ID] = 3
|| JobSubmissions[JS_ID] = 4)
&& JobSubmissions[SourceID] = 2
)
)

26. .Total Sales Rep Interview =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
(JobSubmissions[JS_ID] = 2
|| JobSubmissions[JS_ID] = 3
|| JobSubmissions[JS_ID] = 4)
&& JobSubmissions[SourceID] = 2
)
)

27. .Total Sales Rep Submission_new =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
( JobSubmissions[JS_ID] = 1
|| JobSubmissions[JS_ID] = 6
|| JobSubmissions[JS_ID] = 12
|| JobSubmissions[JS_ID] = 15)
&& JobSubmissions[SourceID] = 2
)
)

28. .Total Student Interview =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
(JobSubmissions[JS_ID] = 2
|| JobSubmissions[JS_ID] = 3
|| JobSubmissions[JS_ID] = 4)
&& JobSubmissions[SourceID] = 1
)
)

29. .Total Student Sub Interested =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
(JobSubmissions[JS_ID] = 13
|| JobSubmissions[JS_ID] = 14)
&& JobSubmissions[SourceID] = 1
)
)

30. .Total Student Submission_new =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
( JobSubmissions[JS_ID] = 1
|| JobSubmissions[JS_ID] = 6
|| JobSubmissions[JS_ID] = 12
|| JobSubmissions[JS_ID] = 15)
&& JobSubmissions[SourceID] = 1
)
)

31. .TTM_Hire.refactor_interest =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Hire.refactor Interested],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result

32. .TTM_Hire.refactor_JobInterview =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Hire.refactored.ai Interview],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result

33. .TTM_Hire.refactor_Jobsubmission =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Hire.refactored.ai Submission _new],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result

34. .TTM_Huntr_Interest =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Huntr Interested],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result

35. .TTM_Huntr_JobInterview =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Huntr Interview],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result

36..TTM_Huntr_Jobsubmission =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Huntr Submission],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result

37. .TTM_Huntr_Jobsubmission new =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Huntr Submission],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result

38. .TTM_Interest to submission =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
DIVIDE([Total Submission], [Total Interest], 0),
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result + 0

39. .TTM_Interview to Placement =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
DIVIDE([Total PLacement], [Total Interview], 0),
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result

40. .TTM_Sales Rep_JobInterview =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Sales Rep Interview],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result

41. .TTM_Sales Reps' interest =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Sales Rep Interested],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result

42. .TTM_Sales Reps'_Jobsubmission =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Sales Rep Submission_new],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result

43. .TTM_Student Sub_interest =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Student Sub Interested],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result

44. .TTM_Student_JobInterview =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Student Interview],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result

45. .TTM_Student_Jobsubmission =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Student Submission_new],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result

46. .TTM_Submission_Interview =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
DIVIDE([Total Interview],[Total Submission], 0),
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result

47. CURRENTWEEK =
SELECTEDVALUE ( 'Date'[Week Number] )

48. Interest to Submission =
VAR _TOTALSUBMITTED =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER ( JobSubmissions,
JobSubmissions[JS_ID] IN {1, 6, 12, 15}
)
)
VAR _TOTALINTERESTED =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
JobSubmissions[JS_ID] = 13
|| JobSubmissions[JS_ID] = 14
)
)
RETURN
DIVIDE ( _TOTALSUBMITTED, _TOTALINTERESTED, 0 ) + 0

49. Submission to Interview Ratio = DIVIDE([Total Interview],[Total Submission],0) + 0

50. TTM_ Interest =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[ID] ),
FILTER (
JobSubmissions,
JobSubmissions[JS_ID] = 13
|| JobSubmissions[JS_ID] = 14
)
)
RETURN
Result

51. TTM_ Interview =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[ID] ),
FILTER (
JobSubmissions,
JobSubmissions[JS_ID] = 2
|| JobSubmissions[JS_ID] = 3
|| JobSubmissions[JS_ID] = 4
)
)
RETURN
Result

52. TTM_ Placement =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[ID] ),
FILTER (
JobSubmissions,
JobSubmissions[JS_ID] = 7
|| JobSubmissions[JS_ID] = 8
)
)
RETURN
Result

53. TTM_Interview to Placement =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
DIVIDE([Total PLacement],[Total Interview],0),
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result

54. TTM_Jobsubmission =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
CALCULATE(DISTINCTCOUNT(JobSubmissions[ID]),FILTER(JobSubmissions,JobSubmissions[JS_ID]=1)),
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result

55. Total Jobs = ROUND(DISTINCTCOUNT(JobSubmissions[JobID]),0)

56. Total PLacement =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
JobSubmissions[JS_ID] = 7
|| JobSubmissions[JS_ID] = 8
)
)

57. Total Submission =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER ( JobSubmissions, JobSubmissions[JS_ID] = 1
|| JobSubmissions[JS_ID] = 6
|| JobSubmissions[JS_ID] = 12
|| JobSubmissions[JS_ID] = 15)
)

58. Interview to Placement Ratio New = DIVIDE([Total PLacement],[Total Interview],7) + 0

59. Interview to Placement Ratio = DIVIDE([Total PLacement],[Total Interview],0) + 0

60. Interest to submission1 = DIVIDE([Total Interest],[Total Submission],0) + 0


To learn more about how to create Measures in power Bi, Click the link below:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-measures